package com.lin.sqltool.service.impl;



import com.lin.sqltool.common.SqlExecutionResult;
import com.lin.sqltool.model.*;
import com.lin.sqltool.service.ConnectionService;
import javafx.scene.control.Tab;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MySQLConnectionServiceImpl implements ConnectionService {


    /**
     * 创建连接
     *
     * @param info     连接信息
     * @return 连接
     */
    private Connection createConnectionUrl(ConnectionInfoModel info) throws SQLException {
        String baseUrl = "jdbc:mysql://" + info.getHost() + ":" + info.getPort();
        String username = info.getUsername();
        String password = info.getPassword();
        return DriverManager.getConnection(baseUrl, username, password);
    }

    /**
     * 创建连接
     *
     * @param info     连接信息
     * @return 连接
     */
    private Connection createConnectionUrl(ConnectionInfoModel info , String database) throws SQLException {
        String baseUrl = "jdbc:mysql://" + info.getHost() + ":" + info.getPort() + "/" + database;
        String username = info.getUsername();
        String password = info.getPassword();
        return DriverManager.getConnection(baseUrl, username, password);
    }



    /**
     * 测试连接
     *
     * @param connectionInfoModel
     * @return
     */
    @Override
    public boolean testConnection(ConnectionInfoModel connectionInfoModel) {
        try (Connection connection = createConnectionUrl(connectionInfoModel)) {
            return connection.isValid(20); // 超时时间为 5 秒
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 查询所有数据库
     * @param info 连接信息
     * @return 数据库名称列表
     */
    @Override
    public List<String> getDatabases(ConnectionInfoModel info) {
        List<String> databases = new ArrayList<>();
        try (Connection connection = createConnectionUrl(info);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SHOW DATABASES")) {
            while (resultSet.next()) {
                databases.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return databases;
    }

    /**
     * 查询指定数据库中的所有表
     * @param info 连接信息
     * @param database 数据库名称
     * @return 表名称列表
     */
    @Override
    public List<String> getTables(ConnectionInfoModel info, String database) {
        List<String> tables = new ArrayList<>();
        try (Connection connection =  createConnectionUrl(info,database);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SHOW TABLES")) {
            while (resultSet.next()) {
                tables.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tables;
    }

    /**
     * 获取指定表的 DDL 信息
     * @param info 连接信息
     * @param database 数据库名称
     * @param table 表名称
     * @return 表的 DDL 信息
     */
    public String getTableDDL(ConnectionInfoModel info, String database, String table) {
        String ddl = null;
        try (Connection connection =  createConnectionUrl(info,database);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SHOW CREATE TABLE " + table)) {
            if (resultSet.next()) {
                ddl = resultSet.getString(2);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ddl;
    }


    /**
     * 获取表结构信息（字段详细信息，包含注释）
     *
     * @param info     连接信息
     * @param database 数据库名称
     * @param table    表名称
     * @return 表结构信息列表
     */
    @Override
    public List<FieldInfoModel> getTableColumns(ConnectionInfoModel info, String database, String table) {
        List<FieldInfoModel> columns = new ArrayList<>();
        try (Connection connection =  createConnectionUrl(info,database);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SHOW FULL COLUMNS FROM " + table)) {
            while (resultSet.next()) {
                FieldInfoModel column = new FieldInfoModel();
                column.setName(resultSet.getString("Field"));
                column.setType(resultSet.getString("Type"));
                column.setComment(resultSet.getString("Comment"));
                columns.add(column);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return columns;
    }


    /**
     * 获取表索引信息，包含索引类型
     *
     * @param info     连接信息
     * @param database 数据库名称
     * @param table    表名称
     * @return 索引信息列表
     */
    @Override
    public List<IndexInfoModel> getTableIndexes(ConnectionInfoModel info, String database, String table) {
        List<IndexInfoModel> indexes = new ArrayList<>();
        try (Connection connection = createConnectionUrl(info, database);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SELECT INDEX_NAME, NON_UNIQUE, COLUMN_NAME, INDEX_TYPE " +
                     "FROM INFORMATION_SCHEMA.STATISTICS " +
                     "WHERE TABLE_SCHEMA = '" + database + "' AND TABLE_NAME = '" + table + "'")) {
            while (resultSet.next()) {
                IndexInfoModel index = new IndexInfoModel();
                index.setName(resultSet.getString("INDEX_NAME"));
                index.setType(resultSet.getString("INDEX_TYPE"));
                index.setFields(resultSet.getString("COLUMN_NAME"));
                indexes.add(index);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return indexes;
    }

    /**
     * 执行sql
     *
     * @param info
     * @param database
     * @param sql
     * @return
     */
    @Override
    public SqlExecutionResult executeSql(ConnectionInfoModel info, String database, String sql) {
        try (
                Connection connection = createConnectionUrl(info, database);
                Statement statement = connection.createStatement();
        ) {
            String trimmedSql = sql.trim().toLowerCase();
            if (trimmedSql.startsWith("select") ||  trimmedSql.startsWith("show")) {
                ResultSet resultSet = statement.executeQuery(sql);
                // 处理查询语句
                List<List<String>> tableData = new ArrayList<>();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();

                // 存储表头
                List<String> headers = new ArrayList<>();
                for (int i = 1; i <= columnCount; i++) {
                    headers.add(metaData.getColumnName(i));
                }
                tableData.add(headers);

                // 存储数据行
                while (resultSet.next()) {
                    List<String> row = new ArrayList<>();
                    for (int i = 1; i <= columnCount; i++) {
                        row.add(resultSet.getString(i));
                    }
                    tableData.add(row);
                }

                return new SqlExecutionResult(true, tableData, null);
            } else {
                // 处理非查询语句
                int rowsAffected = statement.executeUpdate(sql);
                return new SqlExecutionResult(false, null, "执行成功，影响行数: " + rowsAffected);
            }

        } catch (SQLException e) {
            return new SqlExecutionResult(false, null, "执行失败 : "+e.getMessage());
        }
    }


    /**
     * 执行sql
     *
     * @param info
     * @param database
     * @param sql
     * @param tab
     * @return
     */
    @Override
    public SqlExecutionResult executeSql(ConnectionInfoModel info, String database, String sql, Tab tab) {
        return null;
    }

    private Map<String,DatabaseInfoModel> databaseInfoModelCache = new HashMap<String, DatabaseInfoModel>();


    /**
     * 获取数据库信息
     *
     * @param info
     * @param databaseName
     * @return
     */
    @Override
    public DatabaseInfoModel getDatabaseInfo(ConnectionInfoModel info, String databaseName) {
        String key = info.getHost() + ":" + info.getPort() + ":" + databaseName;
        if (databaseInfoModelCache.containsKey(key)) {
            return databaseInfoModelCache.get(key);
        }

        try (Connection connection = createConnectionUrl(info, databaseName)) {
            DatabaseInfoModel databaseInfo = new DatabaseInfoModel(databaseName);

            // 获取数据库中所有表的名称
            try (Statement tableStatement = connection.createStatement();
                 ResultSet tableResultSet = tableStatement.executeQuery("SHOW TABLES")) {
                while (tableResultSet.next()) {
                    String tableName = tableResultSet.getString(1);
                    TableInfoModel tableInfo = new TableInfoModel(tableName);

                    // 获取表的字段信息
                    List<FieldInfoModel> fields = new ArrayList<>();
                    try (Statement columnStatement = connection.createStatement();
                         ResultSet columnResultSet = columnStatement.executeQuery("SHOW FULL COLUMNS FROM " + tableName)) {
                        while (columnResultSet.next()) {
                            FieldInfoModel field = new FieldInfoModel();
                            field.setName(columnResultSet.getString("Field"));
                            field.setType(columnResultSet.getString("Type"));
                            field.setComment(columnResultSet.getString("Comment"));
                            field.setPrimaryKey("PRI".equals(columnResultSet.getString("Key")));
                            fields.add(field);
                        }
                    }
                    tableInfo.setFields(fields);

                    // 获取表的索引信息
                    List<IndexInfoModel> indexes = new ArrayList<>();
                    try (Statement indexStatement = connection.createStatement();
                         ResultSet indexResultSet = indexStatement.executeQuery(
                                 "SELECT INDEX_NAME, NON_UNIQUE, COLUMN_NAME, INDEX_TYPE " +
                                 "FROM INFORMATION_SCHEMA.STATISTICS " +
                                 "WHERE TABLE_SCHEMA = '" + databaseName + "' AND TABLE_NAME = '" + tableName + "'")) {
                        while (indexResultSet.next()) {
                            IndexInfoModel index = new IndexInfoModel(
                                    indexResultSet.getString("INDEX_NAME"),
                                    indexResultSet.getString("INDEX_TYPE"),
                                    indexResultSet.getString("COLUMN_NAME")
                            );
                            indexes.add(index);
                        }
                    }
                    tableInfo.setIndexes(indexes);

                    // 将表信息添加到数据库信息中
                    databaseInfo.addTable(tableInfo);
                }
            }
            databaseInfoModelCache.put(key, databaseInfo);
            return databaseInfo;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }


}
